﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using System.Data.SqlTypes;
using System.Reflection;

namespace Lookstone.Data.SQLite
{
	/// <summary>
	/// Summary description for SqlBase.
	/// </summary>
	public abstract class SQLiteBase
	{
		private bool _suppressEvents = false;
		private bool _isNewRecord = true;

		/// <summary>
		/// Default constructor sets the object up as a new record
		/// </summary>
		public SQLiteBase()
		{
		}

		public SQLiteBase(SQLiteDataReader reader)
			: this(new RowReader(reader)) { }

		/// <summary>
		/// Constructor that reads the record data immediately
		/// </summary>
		/// <param name="reader"></param>
		public SQLiteBase(RowReader reader)
		{
			LoadRowData(reader);
			_isNewRecord = false;
		}

		#region /****** Properties ******/
		/// <summary>
		/// Override to provide better information at derived levels
		/// </summary>
		public virtual bool IsNewRecord
		{
			get { return _isNewRecord; }
			set { _isNewRecord = value; }
		}

		/// <summary>
		/// gets or sets whether events that are triggered by the SqlBase 
		/// and derived objects should be suppressed
		/// </summary>
		public bool SuppressEvents
		{
			get { return _suppressEvents; }
			set { _suppressEvents = value; }
		}
		#endregion /****** Properties ******/

		/// <summary>
		/// Override to load the instance data for the fields of a 
		/// derived object
		/// </summary>
		/// <param name="reader">RowReader containing an active record</param>
		protected abstract void LoadRowData(RowReader reader);

		/// <summary>
		/// Override to handle custom saving information for an Insert / Update
		/// </summary>
		/// <param name="reader">RowReader object containing results from database</param>
		protected virtual void StoreSaveInfo(RowReader reader)
		{
		}

		/// <summary>
		/// Override to provide a custom Load command for an object. This is 
		/// required in order to use the SqlBase(object id) constructor
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		protected virtual SQLiteCommand GetLoadCommand()
		{
			return null;
		}

		/// <summary>
		/// Override to provide a custom Insert / Update command object
		/// </summary>
		/// <returns>Valid SQLiteCommand object or null</returns>
		protected virtual SQLiteCommand GetSaveCommand()
		{
			return null;
		}

		/// <summary>
		/// Override to provide a custom Delete command object
		/// </summary>
		/// <returns></returns>
		protected virtual SQLiteCommand GetDeleteCommand()
		{
			return null;
		}

		/// <summary>
		/// Loads the record specified in the constructor using the 
		/// passed in SQLiteConnectionContext object
		/// </summary>
		/// <param name="context">SQLiteConnectionContext used for query</param>
		protected virtual void Load(SQLiteConnectionContext context)
		{
			// open the database to retrieve info
			SQLiteCommand cmd = GetLoadCommand();

			// If there isn't a command object returned, 
			// get the Sql query
			if (cmd == null)
				throw new Exception("No Load Command defined. Override GetLoadCommand.");

			try
			{
				context.ConnectCommand(cmd);

				using (IDataReader reader = cmd.ExecuteReader())
				{
					if (reader.Read())
					{
						LoadRowData(new RowReader(reader));
						_isNewRecord = false;
					}
					else // If no records were found, set to be a new record
					{
						_isNewRecord = true;
					}

					reader.Close();
				}
			}
			finally
			{
				cmd.Dispose();
			}
		}

		/// <summary>
		/// Saves this instance of the record
		/// </summary>
		/// <param name="context">SQLiteConnectionContext used to execute the query</param>
		/// <remarks>SQLite best practice is to make sure any inserts and updates are wrapped by a transaction</remarks>
		public virtual void Save(SQLiteConnectionContext context)
		{
			SQLiteCommand cmd = GetSaveCommand();
			bool isNew = IsNewRecord;

			if (cmd == null)
				throw new Exception("No Save Command defined. Override GetSaveCommand.");

			try
			{
				context.ConnectCommand(cmd);

				using (IDataReader reader = cmd.ExecuteReader())
				{
					if (reader.Read())
						StoreSaveInfo(new RowReader(reader));
					reader.Close();
				}

				// Change to a saved record no matter what
				_isNewRecord = false;

				// Don't send events out when suppressed
				if (!_suppressEvents && !context.IsTransacting)
				{
					DataEvent.EventType action;

					if (isNew)
						action = DataEvent.EventType.Add;
					else
						action = DataEvent.EventType.Edit;

					FireEvent(action, context);
				}
			}
			catch (Exception ex)
			{
				System.Diagnostics.Trace.WriteLine(string.Format("Error: {0}\nStack: {1}", ex.Message, ex.StackTrace));
				throw new DataException(string.Format("Unable to save record: {0}", ex.Message), ex);
			}
			finally
			{
				cmd.Dispose();
			}
		}

		/// <summary>
		/// Deletes the row corresponding to this instance in the data source
		/// </summary>
		/// <param name="context">SQLiteConnectionContext used to execute the query</param>
		public virtual void Delete(SQLiteConnectionContext context)
		{
			SQLiteCommand sqlCommand = GetDeleteCommand();

			if (sqlCommand == null)
				throw new Exception("No Delete Command defined. Override GetDeleteCommand.");

			try
			{
				context.ConnectCommand(sqlCommand);
				sqlCommand.ExecuteNonQuery();
			}
			finally
			{
				sqlCommand.Dispose();
			}

			if (!_suppressEvents && !context.IsTransacting)
				FireEvent(DataEvent.EventType.Delete, context);
		}

		public void FireEvent(DataEvent.EventType eventType, SQLiteConnectionContext context)
		{
			DataEvent.DataChangeEventArgs args = new DataEvent.DataChangeEventArgs(eventType, context);
			args.EventObject = this;
			DataEvent.InvokeChangeEvent(this, args);
		}

		/// <summary>
		/// Fills a DataSet object based on the passed in select command
		/// </summary>
		/// <param name="command">Command object that contains a valid 
		/// connection / transaction and query command text</param>
		/// <returns>A filled dataset</returns>
		public static DataSet SelectIntoDataSet(SQLiteCommand command)
		{
			SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
			DataSet results = new DataSet("SelectResults");

			SelectIntoDataSet(command, results, "Results");
			return results;
		}

		/// <summary>
		/// Fills a DataSet object based on the passed in select command
		/// </summary>
		/// <param name="command">Command object that contains a valid 
		/// connection / transaction and query command text</param>
		/// <param name="dataSet">DataSet to be filled</param>
		/// <param name="tableName">A </param>
		public static void SelectIntoDataSet(SQLiteCommand command, DataSet dataSet, string tableName)
		{
			using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command))
			{
				// Assume that the connection has already been set up and any transactions have been attached
				if (!dataSet.Tables.Contains(tableName))
					dataSet.Tables.Add(tableName);

				adapter.Fill(dataSet, tableName);
			}
		}

		/// <summary>
		/// Loads 0-n records at a time by a type derived from SqlBase.
		/// </summary>
		/// <typeparam name="T">Type of record to instantiate</typeparam>
		/// <param name="cmd">Reference to the SQLiteCommand that is the source of the objects to be created</param>
		/// <returns>List (of T) containing all built records</returns>
		public static List<T> LoadBulk<T>(SQLiteCommand cmd) where T : SQLiteBase, new()
		{
			List<T> records = new List<T>();

			using (SQLiteDataReader reader = cmd.ExecuteReader())
			{
				RowReader row = new RowReader(reader);

				while (reader.Read())
				{
					T obj = new T();

					obj.LoadRowData(row);
					obj.IsNewRecord = false;
					records.Add(obj);
				}
			}

			return records;
		}
	}
}